﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Reflection;


namespace mluDataEntity
{
    public class Count
    {
        public DataTable ClientiDepositati()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var count = (from c in context.clienti
                                 join d in context.depositati
                                    on c.ID equals d.IdCliente
                                 group c by c into g
                                 select new
                                 {
                                     g.Key.Nome,
                                     g.Key.Cognome,

                                     count = g.Count()
                                 }
                        );

                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = count.First();
                    propInfo = first.GetType().GetProperties();


                    foreach (PropertyInfo info in propInfo)
                    {
                        if (!info.PropertyType.FullName.Contains("DateTime"))
                            dt.Columns.Add(info.Name, info.PropertyType);
                        else
                            dt.Columns.Add(info.Name, typeof(DateTime));
                    }

                    foreach (var index in count)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            dr[prop.Name] = prop.GetValue(index);
                        }
                        dt.Rows.Add(dr);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public DataTable ClientiLibriVenduti()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var count = (from cl in context.clienti
                                 join d in context.depositati
                                    on cl.ID equals d.IdCliente
                                 join ca in context.carrelli
                                    on d.IdMLU equals ca.idmlu
                                 group cl by cl into g
                                 select new
                                 {
                                     g.Key.Nome,
                                     g.Key.Cognome,

                                     count = g.Count()
                                 }
                        );

                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = count.First();
                    propInfo = first.GetType().GetProperties();


                    foreach (PropertyInfo info in propInfo)
                    {
                        if (!info.PropertyType.FullName.Contains("DateTime"))
                            dt.Columns.Add(info.Name, info.PropertyType);
                        else
                            dt.Columns.Add(info.Name, typeof(DateTime));
                    }

                    foreach (var index in count)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            dr[prop.Name] = prop.GetValue(index);
                        }
                        dt.Rows.Add(dr);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public DataTable ClientiDepositatiVenduti()
        {
            using (mluEntities context = new mluEntities())
            {
                try
                {
                    var count = (from cl in context.clienti
                                 join d in context.depositati   on cl.ID equals d.IdCliente         into clientidepositati
                                    from d in clientidepositati
                                 join ca in context.carrelli    on d.IdMLU equals ca.idmlu          into depositatiCarrelli
                                    from ca in depositatiCarrelli.DefaultIfEmpty()
                                
                                 select new 
                                 {
                                  cl.Nome,
                                  cl.Cognome,
                                  numeroDepositati=clientidepositati.Count(),
                                  numeroVenduti=depositatiCarrelli.Count()
                                 }
                        );



                    System.Reflection.PropertyInfo[] propInfo = null;
                    DataTable dt = new DataTable();
                    var first = count.First();
                    propInfo = first.GetType().GetProperties();


                    foreach (PropertyInfo info in propInfo)
                    {
                        if (!info.PropertyType.FullName.Contains("DateTime"))
                            dt.Columns.Add(info.Name, info.PropertyType);
                        else
                            dt.Columns.Add(info.Name, typeof(DateTime));
                    }

                    foreach (var index in count)
                    {
                        DataRow dr = dt.NewRow();
                        foreach (System.Reflection.PropertyInfo prop in propInfo)
                        {
                            object obj=prop.GetValue(index);
                            if (obj != null && obj!=DBNull.Value)
                                dr[prop.Name] = obj;
                            else
                                dr[prop.Name] = DBNull.Value;
                        }
                        dt.Rows.Add(dr);
                    }

                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
    }
}

